// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using Kailua.net.windward.utils;
using net.windward.utils.ado.MySql;
using NUnit.Framework;

namespace net.windward.utils.ado
{
    // bugbug - not testing SET or ENUM type.
    [TestFixture]
    public class TestMySqlProvider
    {
        private string serverName;
        private string databaseName;
        private string username;
        private string password;

        readonly DbProviderFactory providerFactory = DbProviderFactories.GetFactory(WrMySqlVendor.FactoryClass);


        [SetUp]
        public void Init()
        {
            NameValueCollection appSettings = ConfigurationManager.AppSettings;
            serverName = appSettings["MySqlServer"];
            databaseName = appSettings["MySqlDatabase"];
            username = appSettings["MySqlUsername"];
            password = appSettings["MySqlPassword"];
        }

        [Test]
        public void TestCreateProvider()
        {

            Assert.IsTrue(new WrMySqlVendor().IsInstalled);

            WrMySqlVendor vendor = (WrMySqlVendor)WrProviderFactories.CreateProvider("My Sql name", null, WrMySqlVendor.FactoryClass);
            Assert.IsNotNull(vendor);
            Assert.AreEqual(true, vendor.CanEnumerateDatabases);
            Assert.AreEqual(false, vendor.CanEnumerateServers);
            // bugbug: CanLaunchAdministrator returns false.
            //Assert.AreEqual(true, vendor.CanLaunchAdministrator);
            Assert.AreEqual(true, vendor.CanRunScripts);
            Assert.AreEqual(false, vendor.CanUseTrustedConnection);
			Assert.AreEqual(WrVendor.DATABASE_MODE.REQUIRED, vendor.DatabaseMode);
			Assert.AreEqual(false, vendor.HasProviders);
            Assert.AreEqual("My Sql name", vendor.Name);
            Assert.AreEqual("server=abc;database=def;user id=123;password=456",
				vendor.ConnectionString("abc", "def", new WrCredentials("123", "456"), true).ToLower());
        }

        [Test]
        public void TestSakilaEnumeration()
        {

            // enum databases, make sure has test one
            WrMySqlServer server = new WrMySqlServer(providerFactory, serverName);
            Assert.AreEqual(serverName, server.Name);
            IWrDatabase[] databases = server.GetDatabases(new WrCredentials(username, password));
            Assert.IsTrue(databases.Length > 0);
            WrMySqlDatabase db = null;
            foreach (IWrDatabase dbOn in databases)
            {
                Assert.IsTrue(dbOn is WrMySqlDatabase);
                if (dbOn.Name == databaseName)
                    db = (WrMySqlDatabase)dbOn;
            }
            Assert.IsNotNull(db);

            // Sakila tables
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.ALL, null);
        	WrTable[] tables = db.Tables;
			Assert.AreEqual(16, tables.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.SYSTEM, null);
        	tables = db.Tables;
			Assert.AreEqual(0, tables.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	tables = db.Tables;
            Assert.AreEqual(16, tables.Length);
            Assert.AreEqual("actor", tables[0].FullName);
            Assert.IsFalse(tables[0].IsSystem);
            Assert.AreEqual("", tables[0].Description);
            Assert.AreEqual("store", tables[15].FullName);
            Assert.IsFalse(tables[15].IsSystem);
            Assert.AreEqual("", tables[15].Description);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
        	tables = db.Tables;
            Assert.AreEqual(16, tables.Length);
            Assert.AreEqual("actor", tables[0].FullName);
            Assert.IsFalse(tables[0].IsSystem);
            Assert.IsTrue(tables[0].Description.StartsWith("InnoDB free:"));
            Assert.AreEqual("store", tables[15].FullName);
            Assert.IsFalse(tables[15].IsSystem);
            Assert.IsTrue(tables[15].Description.StartsWith("InnoDB free:"));

            // Sakila views
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.ALL, null);
        	WrView[] views = db.Views;
            Assert.AreEqual(7, views.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.SYSTEM, null);
			views = db.Views;
			Assert.AreEqual(0, views.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			views = db.Views;
			Assert.AreEqual(7, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
            Assert.IsFalse(views[0].IsSystem);
            Assert.AreEqual("", views[0].Description);
            Assert.AreEqual("staff_list", views[6].FullName);
            Assert.IsFalse(views[6].IsSystem);
            Assert.AreEqual("", views[0].Description);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
			views = db.Views;
            Assert.AreEqual(7, views.Length);
            Assert.AreEqual("actor_info", views[0].FullName);
            Assert.IsFalse(views[0].IsSystem);
            Assert.AreEqual("VIEW", views[0].Description);
            Assert.AreEqual("staff_list", views[6].FullName);
            Assert.IsFalse(views[6].IsSystem);
            Assert.AreEqual("VIEW", views[0].Description);

            // Sakila stored procedures
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.ALL, null);
        	WrStoredProcedure[] procs = db.StoredProcedures;
            Assert.AreEqual(3, procs.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.SYSTEM, null);
        	procs = db.StoredProcedures;
			Assert.AreEqual(0, procs.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	procs = db.StoredProcedures;
			Assert.AreEqual(3, procs.Length);
			Assert.AreEqual("film_in_stock", procs[0].FullName);
            Assert.IsFalse(procs[0].IsSystem);
            Assert.AreEqual("", procs[0].Description);
            Assert.AreEqual("rewards_report", procs[2].FullName);
            Assert.IsFalse(procs[2].IsSystem);
            Assert.AreEqual("", procs[2].Description);
			Assert.AreEqual(3, procs[2].Parameters.Length);
			Assert.AreEqual("min_monthly_purchases", procs[2].Parameters[0].Name);
			Assert.AreEqual(ParameterDirection.Input, procs[2].Parameters[0].Direction);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
			procs = db.StoredProcedures;
            Assert.AreEqual(3, procs.Length);
            Assert.AreEqual("film_in_stock", procs[0].FullName);
            Assert.IsFalse(procs[0].IsSystem);
            Assert.AreEqual("", procs[0].Description);
            Assert.AreEqual("rewards_report", procs[2].FullName);
            Assert.IsFalse(procs[2].IsSystem);
            Assert.AreEqual("Provides a customizable report on best customers", procs[2].Description);

            // Sakila.staff columns
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	WrColumn[] cols = db.Tables[14].Columns;
            Assert.AreEqual(11, cols.Length);
            Assert.AreEqual("staff_id", cols[0].FullName);
            Assert.AreEqual(DbType.Byte, cols[0].DbType);
            Assert.AreEqual("", cols[0].Description);
            Assert.AreEqual("password", cols[9].FullName);
            Assert.AreEqual(DbType.String, cols[9].DbType);
            Assert.AreEqual("", cols[9].Description);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
        	cols = db.Tables[14].Columns;
            Assert.AreEqual(11, cols.Length);
            Assert.AreEqual("staff_id", cols[0].FullName);
            Assert.AreEqual(DbType.Byte, cols[0].DbType);
            Assert.AreEqual("", cols[0].Description);
            Assert.AreEqual("password", cols[9].FullName);
            Assert.AreEqual(DbType.String, cols[9].DbType);
            Assert.AreEqual("", cols[9].Description);

			// test rental == staff.staff_id pk-fk relationships were extracted
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
        	cols = db.Tables[13].Columns;
            Assert.AreEqual("staff.staff_id", cols[5].PrimaryKey);

			// KailuaTest - AllTypes
            db = new WrMySqlDatabase(providerFactory, serverName, "KailuaTest");
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	cols = db.Tables[0].Columns;
            Assert.AreEqual(17, cols.Length);
            Assert.AreEqual("pk", cols[0].FullName);
            Assert.AreEqual(DbType.Int64, cols[0].DbType);
            Assert.AreEqual("kTinyInt", cols[1].FullName);
            Assert.AreEqual(DbType.Byte, cols[1].DbType);
            Assert.AreEqual("kSmallInt", cols[2].FullName);
            Assert.AreEqual(DbType.Int16, cols[2].DbType);
            Assert.AreEqual("kMediumInt", cols[3].FullName);
            Assert.AreEqual(DbType.Int32, cols[3].DbType);
            Assert.AreEqual("kInteger", cols[4].FullName);
            Assert.AreEqual(DbType.Int32, cols[4].DbType);
            Assert.AreEqual("kBigInt", cols[5].FullName);
            Assert.AreEqual(DbType.Int64, cols[5].DbType);
            Assert.AreEqual("kDateTime", cols[6].FullName);
            Assert.AreEqual(DbType.DateTime, cols[6].DbType);
            Assert.AreEqual("kDate", cols[7].FullName);
            Assert.AreEqual(DbType.DateTime, cols[7].DbType);
            Assert.AreEqual("kTimeStamp", cols[8].FullName);
            Assert.AreEqual(DbType.DateTime, cols[8].DbType);
            Assert.AreEqual("kTime", cols[9].FullName);
            Assert.AreEqual(DbType.Object, cols[9].DbType);
            Assert.AreEqual("kYear", cols[10].FullName);
            Assert.AreEqual(DbType.Int32, cols[10].DbType);
            Assert.AreEqual("kChar", cols[11].FullName);
            Assert.AreEqual(DbType.String, cols[11].DbType);
            Assert.AreEqual("kVarChar", cols[12].FullName);
            Assert.AreEqual(DbType.String, cols[12].DbType);
            Assert.AreEqual("kBinary", cols[13].FullName);
            Assert.AreEqual(DbType.String, cols[13].DbType);
            Assert.AreEqual("kVarBinary", cols[14].FullName);
            Assert.AreEqual(DbType.String, cols[14].DbType);
            Assert.AreEqual("kBlob", cols[15].FullName);
            Assert.AreEqual(DbType.Binary, cols[15].DbType);
            Assert.AreEqual("kText", cols[16].FullName);
            Assert.AreEqual(DbType.String, cols[16].DbType);
        }

        [Test]
        public void TestNames()
        {
            // test identifiers with spaces and keywords

            WrMySqlSyntax syntax = new WrMySqlSyntax(null);
            Assert.AreNotEqual(null, syntax.SafeColumnName("add"));
            Assert.AreEqual("`add`", syntax.SafeColumnName("add"));
            Assert.AreEqual("`table name`", syntax.SafeColumnName("table name"));
            Assert.AreEqual("no_space", syntax.SafeColumnName("no_space"));
            Assert.AreEqual("`table name`", syntax.SafeColumnName("`table name"));
            Assert.AreEqual("`add`", syntax.SafeColumnName("`add`"));

            // test the standard uses

            databaseName = ConfigManager.AppSettings["MySqlNameTestDb"];

            WrMySqlServer server = new WrMySqlServer(providerFactory, serverName);
            Assert.AreEqual(serverName, server.Name);

            // test GetDatabases
            IWrDatabase[] databases = server.GetDatabases(new WrCredentials(username, password));
            Assert.IsTrue(databases.Length > 0);
            WrMySqlDatabase db = null;
            foreach (IWrDatabase dbOn in databases)
            {
                Assert.IsTrue(dbOn is WrMySqlDatabase);
                if (dbOn.Name == databaseName)
                    db = (WrMySqlDatabase)dbOn;
            }
            Assert.IsNotNull(db);

            // test GetTables
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
        	WrTable[] tables = db.Tables;
            Assert.AreEqual(2, tables.Length);
            Assert.AreEqual("keyword table order", tables[0].FullName);
            Assert.IsFalse(tables[0].IsSystem);
            Assert.AreEqual("spaced name table", tables[1].FullName);
            Assert.IsFalse(tables[1].IsSystem);

            // test GetViews
        	WrView[] views = db.Views;
            Assert.AreEqual(1, views.Length);
            Assert.AreEqual("keyword view order", views[0].FullName);
            Assert.IsFalse(views[0].IsSystem);

            // test GetStoredProcedures
        	WrStoredProcedure[] procs = db.StoredProcedures;
            Assert.AreEqual(1, procs.Length);
            Assert.AreEqual("word cat", procs[0].FullName);
            Assert.IsFalse(procs[0].IsSystem);

			// test Columns - spaced name table
        	WrColumn[] cols = db.Tables[1].Columns;
            Assert.AreEqual(1, cols.Length);
            Assert.AreEqual("spaced name column", cols[0].FullName);

			// test Columns - keyword table order
        	cols = db.Tables[0].Columns;
            Assert.AreEqual(1, cols.Length);
            Assert.AreEqual("keyword column order", cols[0].FullName);
        }

        // bugbug - is this every type?
        // cannot insert a timestamp
        private readonly string[] colNames = {"kTinyInt", "kSmallInt", "kMediumInt", "kInteger", "kBigInt", "kDateTime", "kDate",
				"kTime", "kYear", "kChar", "kVarChar", "kBinary", "kVarBinary", "kBlob", "kText"};
        private readonly object[] colValues = {1, 2, 3, 4, 5, DateTime.Now, DateTime.Now.Date, 
				DateTime.Now.TimeOfDay, 2005, "dave", "shirley", "lucky", "zoe", new byte[8], "cody"};

        [Test]
        public void TestInsert()
        {

            using (WrConnection conn = new WrConnection("MySqlWrite"))
            {
                conn.Open();

                // empty it to start the same
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "delete from AllTypes";
                    cmd.ExecuteNonQuery();
                }

                // test get the PK back
                long[] pk = new long[2];
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "insert into AllTypes (kVarchar) values (?)";
                    cmd.Set(0, "dave");
                    pk[0] = cmd.ExecuteInsert("pk");
                    Assert.IsTrue(pk[0] > 0);
                }

                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "insert into AllTypes (kVarchar) values (?)";
                    cmd.Set(0, "shirley");
                    pk[1] = cmd.ExecuteInsert("pk");
                    Assert.IsTrue(pk[1] > pk[0]);
                }

                // test insert each var type
                for (int ind = 0; ind < colNames.Length; ind++)
                {
                    using (WrCommand cmd = conn.CreateWrCommand())
                    {
                        cmd.CommandText = "insert into AllTypes (" + colNames[ind] + ") values (?)";
                        cmd.Set(0, colValues[ind]);
                        cmd.ExecuteInsert("pk");
                    }
                }

                // write all together
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    StringBuilder command = new StringBuilder("insert into AllTypes (");
                    for (int ind = 0; ind < colNames.Length; ind++)
                        command.Append(colNames[ind] + ",");
                    command.Remove(command.Length - 1, 1);
                    command.Append(") values (");
                    for (int ind = 0; ind < colNames.Length; ind++)
                        command.Append("?,");
                    command.Remove(command.Length - 1, 1);
                    command.Append(")");
                    cmd.CommandText = command.ToString();

                    for (int ind = 0; ind < colNames.Length; ind++)
                        cmd.Set(ind, colValues[ind]);

                    pk[0] = cmd.ExecuteInsert("pk");
                }

                // test read each var type
                byte[] data = new byte[20];
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "select * from AllTypes where pk = ?";
                    cmd.Set(0, pk[0]);
                    using (WrDataReader reader = cmd.WrExecuteReader())
                    {
                        Assert.IsTrue(reader.Read());

                        Assert.AreEqual(pk[0], reader.GetInt64(0));
                        Assert.AreEqual(1, reader.GetByte(1));
                        Assert.AreEqual(2, reader.GetInt16(2));
                        Assert.AreEqual(3, reader.GetInt32(3));
                        Assert.AreEqual(4, reader.GetInt32(4));
                        Assert.AreEqual(5, reader.GetInt64(5));
                        Assert.AreEqual(DateTime.Now.Date, reader.GetDateTime(6).Date);
                        Assert.AreEqual(DateTime.Now.Date, reader.GetDateTime(7).Date);
                        Assert.IsTrue(reader.GetTimestamp(8) > 0);
                        Assert.IsNotNull((TimeSpan)reader.GetValue(9));
                        Assert.AreEqual(2005, reader.GetInt64(10));
                        Assert.AreEqual("dave", reader.GetString(11));
                        Assert.AreEqual("shirley", reader.GetString(12));
                        Assert.AreEqual("lucky", reader.GetString(13).Substring(0, 5));
                        Assert.AreEqual("zoe", reader.GetString(14).Substring(0, 3));
                        Assert.AreEqual(4, reader.GetBytes(15, 0, data, 0, 4));
                        Assert.AreEqual("cody", reader.GetString(16));

                        // remaining calls
                        Assert.IsFalse(reader.IsDBNull(1));
                        Assert.AreEqual("kSmallInt", reader.GetName(2));
                        Assert.AreEqual("INT", reader.GetDataTypeName(3));
                        Assert.AreEqual(typeof(DateTime), reader.GetFieldType(6));
                        Assert.AreEqual(5, reader.GetOrdinal("kBigInt"));
                        Assert.AreEqual(17, reader.FieldCount);
                        Assert.AreEqual("shirley", reader[12]);
                        Assert.AreEqual("shirley", reader["kVarChar"]);

                        object[] all = new object[17];
                        Assert.AreEqual(17, reader.GetValues(all));
                        Assert.AreEqual(pk[0], all[0]);
                        Assert.AreEqual("shirley", all[12]);
                    }
                }

                // test select top N
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "  select   * from AllTypes order by pk DESC  top  1  ";
                    Assert.AreEqual("select   * from AllTypes order by pk DESC limit 1", cmd.CommandText);
                    using (WrDataReader reader = cmd.WrExecuteReader())
                    {
                        Assert.IsTrue(reader.Read());
                        Assert.AreEqual(pk[0], reader.GetInt64(0));
                    }
                }
            }
        }

        //		[Test]
        public void TestLaunch()
        {

            WrMySqlVendor vendor = (WrMySqlVendor)WrProviderFactories.CreateProvider("My Sql name", null, "MySql.Data.MySqlClient");
            Assert.IsTrue(vendor.RunAdministrator());
        }

        private const string script = "..\\..\\DDL_Scripts\\MySql.ddl";

        [Test]
        public void TestScript()
        {
            WrMySqlVendor vendor = (WrMySqlVendor)WrProviderFactories.CreateProvider("My Sql name", null, "MySql.Data.MySqlClient");
            Assert.IsTrue(vendor.RunScript(serverName, databaseName,
                new StreamReader(Path.GetFullPath(script)), new WrCredentials(username, password)));
        }

        [Test]
        public void TestNullUser()
        {
            WrMySqlVendor vendor = new WrMySqlVendor();
            string connStr = "";

            try
            {
				connStr = vendor.ConnectionString(serverName, databaseName, new WrCredentials(null, ""), true);
            }
            catch (ArgumentException aex)
            {
                Assert.Fail("WrMySqlVendor.ConnectionString() failed.\n" + aex.Message);
            }
        }

        [Test]
        public void TestNullPwd()
        {
            WrMySqlVendor vendor = new WrMySqlVendor();
            string connStr = "";

            try
            {
				connStr = vendor.ConnectionString(serverName, databaseName, new WrCredentials("", null), true);
            }
            catch (ArgumentException aex)
            {
                Assert.Fail("WrMySqlVendor.ConnectionString() failed.\n" + aex.Message);
            }
        }

        [Test]
        public void TestCredsEmptyString()
        {
            WrMySqlVendor vendor = new WrMySqlVendor();
            string connStr = "";

            try
            {
				connStr = vendor.ConnectionString(serverName, databaseName, new WrCredentials("", password), true);
            }
            catch (ArgumentException aex)
            {
                Assert.Fail("WrMySqlVendor.ConnectionString() failed.\n" + aex.Message);
            }

            try
            {
				connStr = vendor.ConnectionString(serverName, databaseName, new WrCredentials(username, ""), true);
            }
            catch (ArgumentException aex)
            {
                Assert.Fail("WrMySqlVendor.ConnectionString() failed.\n" + aex.Message);
            }

            try
            {
				connStr = vendor.ConnectionString(serverName, databaseName, new WrCredentials("", ""), true);
            }
            catch (ArgumentException aex)
            {
                Assert.Fail("WrMySqlVendor.ConnectionString() failed.\n" + aex.Message);
            }
        }
    }
}
